﻿using Snowflake.Core;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TianShiProject.EntityFrameworkCore;
using TianShiProject.Models.Common;
using TianShiProject.Models.HumanAffairs.Bases;
using TianShiProject.Models.Materials.RecipientsToAppls;
using Volo.Abp.EntityFrameworkCore;

namespace TianShiProject.HumanAffairs.Bases
{
    public class BaseRepository : DapperDbContextRepository, IBaseRepository
    {
        public BaseRepository(IDbContextProvider<TianShiProjectDbContext> dbContextProvider) : base(dbContextProvider)
        {
        }

        #region 员工登记——查询分页显示
        /// <summary>
        /// 员工登记——查询分页显示
        /// </summary>
        /// <param name="Name"></param>
        /// <param name="Department"></param>
        /// <param name="Job"></param>
        /// <param name="Phone"></param>
        /// <param name="Type"></param>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        public async Task<PageResponse<BaseDto>> GetBasePageShow(string Name,int Department,int Job,string Phone,int Type, int pageindex = 1, int pagesize = 3)
        {
            PageResponse<BaseDto> response = new PageResponse<BaseDto>();
            var sql = "select a.*,b.DeptName as BName,c.PositionName as CName,d.DictionaryName as DName,e.DictionaryName as EName,f.DictionaryName as FName,g.DictionaryName as GName,h.DictionaryName as HName from Base a join Department b on a.Department=b.Id join Position c on a.Job=c.Id join Dictionary d on a.SchoolId=d.Id join Dictionary e on a.MajorId=e.Id join Dictionary f on a.EducationId=f.Id join Dictionary g on a.Type=g.Id join Dictionary h on a.Status=h.Id where a.IsDelete=0";
            if (!string.IsNullOrWhiteSpace(Name))
            {
                sql += $" and a.Name like '%" + Name + "%'";
            }
            if (Department > 0)
            {
                sql += $" and a.Department='{Department}'";
            }
            if (Job > 0)
            {
                sql += $" and a.Job='{Job}'";
            }
            if (!string.IsNullOrWhiteSpace(Phone))
            {
                sql += $" and a.Phone like '%" + Phone + "%'";
            }
            if (Type > 0)
            {
                sql += $" and a.Type='{Type}'";
            }
            var list = await this.QueryAsync<BaseDto>(sql, null);
            response.PageTotal = list.Count;
            sql += $" order by a.Id offset {(pageindex - 1) * pagesize} rows fetch next {pagesize} rows only";
            var lists = await this.QueryAsync<BaseDto>(sql, null);
            response.PageData = lists;
            return response;
        }
        #endregion

        #region 员工登记——逻辑删除
        /// <summary>
        /// 员工登记——逻辑删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<int> DeleteBase(int id)
        {

            var sql = $"update Base set IsDelete=1 where Id={id}";
            return await ExecuteAsync(sql, null);
        }
        #endregion

        #region 员工登记——批量删除
        /// <summary>
        /// 员工登记——批量删除
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public async Task<int> DeleteBatchBase(string ids)
        {
            var list = 0;
            var id = ids.Split(',');
            foreach (var item in id)
            {
                var sql = $"update Base set IsDelete=1 where Id={item}";
                list = await ExecuteAsync(sql, null);
            }
            return list;
        }
        #endregion

        #region 员工登记——编辑
        /// <summary>
        /// 员工登记——编辑
        /// </summary>
        /// <param name="baseDto"></param>
        /// <returns></returns>
        public async Task<int> PutUpdateBase(BaseDto baseDto)
        {
            var sql = $"update Base set BasicNumber='{baseDto.BasicNumber}',Name='{baseDto.Name}',Sex='{baseDto.Sex}',Phone='{baseDto.Phone}',SchoolId='{baseDto.SchoolId}',MajorId='{baseDto.MajorId}',Addr='{baseDto.Addr}',EducationId='{baseDto.EducationId}',Politice='{baseDto.Politice}',Nation='{baseDto.Nation}',Native='{baseDto.Native}',Marriage='{baseDto.Marriage}',Birth='{baseDto.Birth}',Email='{baseDto.Email}',IdCard='{baseDto.IdCard}',Department='{baseDto.Department}',Job='{baseDto.Job}',Type='{baseDto.Type}',IsDelete='{baseDto.IsDelete}' where Id='{baseDto.Id}'";
            return await ExecuteAsync(sql, baseDto);
        }
        #endregion

        #region 员工登记——添加
        /// <summary>
        /// 员工登记——添加
        /// </summary>
        /// <param name="baseDto"></param>
        /// <returns></returns>
        public async Task<int> PostAddBase(BaseDto baseDto)
        {
            var worker = new IdWorker(1, 1);
            var id = worker.NextId().ToString();
            var sql = $"insert into Base values('{id}','{baseDto.Name}','{baseDto.Sex}','{baseDto.Phone}','{baseDto.SchoolId}','{baseDto.MajorId}','{baseDto.Addr}','{baseDto.EducationId}','{baseDto.Politice}','{baseDto.Nation}','{baseDto.Native}','{baseDto.Marriage}','{baseDto.Birth}','{baseDto.Email}','{baseDto.IdCard}','{baseDto.Department}','{baseDto.Job}','{baseDto.Type}',0)";
            return await ExecuteAsync(sql, baseDto);
        }
        #endregion
    }
}
